package db.history;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import db.Database;
import dbservice.history.SaleLogDBService;
import po.AccountBookPo;
import po.Po;
import po.销售明细po;

public class SaleLogDB implements SaleLogDBService{
	private AccountBookPo ab;
	
	
	public SaleLogDB(AccountBookPo ab) throws SQLException{
		String creatTable = "CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_salelog` (  `time` BIGINT(0) NOT NULL,  `name` VARCHAR(45) NOT NULL,  `type` VARCHAR(45) NOT NULL,  `price` INT NOT NULL,  `number` INT NOT NULL,  `sum` INT NOT NULL,`yewuyuan` VARCHAR(45) NOT NULL,`cangku` VARCHAR(45) NOT NULL,`kehu` VARCHAR(45) NOT NULL,  PRIMARY KEY (`time`));";
		this.ab=ab;
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(creatTable);
		s.close();
	} 

	public Po add(销售明细po po){
		Po r=new Po();
		Connection c = Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.execute("insert into "+ab.getName()+"_salelog values('"+po.get时间()+"','"+po.get商品名()+"','"+po.get型号()+"','"+po.get单价()+"','"+po.get数量()+"','"+po.get总额()+"','"+po.get业务员()+"','"+po.get仓库()+"','"+po.get客户()+"');");
			s.close();
		} catch (SQLException e) {
			r.failed(e.getMessage());
			e.printStackTrace();
		}
		return r;
	}

	@Override
	public List<销售明细po> get() {
		List<销售明细po> result=new ArrayList<销售明细po>();		
		try {
			Connection c=Database.getConnection();
			Statement s=c.createStatement();
			ResultSet r= s.executeQuery("select * from "+ab.getName()+"_salelog");
			readResultSet(r,result);
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return result;
	}

	@Override
	public List<销售明细po> get(销售明细po from, 销售明细po to) {
		List<销售明细po> result=new ArrayList<销售明细po>();		
		try {
			Connection c=Database.getConnection();
			Statement s=c.createStatement();
			String whereCase="(time>="+from.get时间()+"&&time<="+to.get时间()+")";
			if(from.get商品名()!=null&&to.get商品名()!=null){
				whereCase=whereCase+"&& (name>='"+from.get商品名()+"'&&name<='"+to.get商品名()+"')";
			}
			if(from.get业务员()!=null&&to.get业务员()!=null){
				whereCase=whereCase+"&& (yewuyuan>='"+from.get业务员()+"'&&yewuyuan<='"+to.get业务员()+"')";
			}
			if(from.get仓库()!=null&&to.get仓库()!=null){
				whereCase=whereCase+"&& (cangku>='"+from.get仓库()+"'&&cangku<='"+to.get仓库()+"')";
			}
			if(from.get客户()!=null&&to.get客户()!=null){
				whereCase=whereCase+"&& (kehu>='"+from.get客户()+"'&&kehu<='"+to.get客户()+"')";
			}
			ResultSet r= s.executeQuery("select * from "+ab.getName()+"_salelog where "+whereCase+";");
			readResultSet(r,result);
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return result;
	}
	
	private void readResultSet(ResultSet r,List<销售明细po> result) throws SQLException{
		while(r.next()){
			销售明细po temp=new 销售明细po();
			temp.set时间(r.getLong(1));
			temp.set商品名(r.getString(2));
			temp.set型号(r.getString(3));
			temp.set单价(r.getLong(4));
			temp.set数量(r.getInt(5));
			temp.set总额(r.getLong(6));
			temp.set业务员(r.getString(7));
			temp.set仓库(r.getString(8));
			temp.set客户(r.getString(9));			
			result.add(temp);
		}
	}
}
